package queries;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import entitys.Customer;
import enums.Result;

public class GetCustomerCharacterizationReportQuery {
	/**
	 * Execute query - get all the calculations needed from DB to produce report.
	 * @param conn
	 * @return ArrayList<String>
	 */
	public static Object exe(Connection conn) {
		ArrayList<String> custWithSold= new ArrayList<String>();
		ArrayList<String> custWithAmount= new ArrayList<String>();
		ArrayList<String> total = new ArrayList<String>();
		ResultSet res = null; 
		Statement stmt;
			try {
				stmt = conn.createStatement();
				
				res = stmt.executeQuery("SELECT sales.custID ,COUNT(DISTINCT custID) FROM ccrm.sales,ccrm.product where sales.productID=product.id AND sales.sold=1 GROUP BY sales.custID;");
				while(res.next())
				{
					custWithSold.add(new String(res.getString(1)+" "+res.getString(2)));
				}
				
				res = stmt.executeQuery("SELECT SALES.custID ,COUNT(custID) FROM ccrm.sales GROUP BY custID ;");
				while(res.next())
				{
					custWithAmount.add(new String(res.getString(1)+" "+res.getString(2)));
					
				}

				} catch (SQLException e) {
					
					e.printStackTrace();
					return null;
				} 
			int j=0;
			float sold;
			float amount;
			float reject;
			for(int i=0;i<custWithAmount.size();i++)
			{
				if(custWithSold.get(j).substring(0, custWithSold.get(j).indexOf(" ")).equals(custWithAmount.get(i).substring(0, custWithAmount.get(i).indexOf(" "))) && j<custWithSold.size())
				{
					sold = Float.parseFloat(custWithSold.get(j).substring(custWithSold.get(j).indexOf(" ")));
					amount = Float.parseFloat(custWithAmount.get(i).substring(custWithAmount.get(i).indexOf(" ")));
					float eff = 100*sold/amount;
				
					if(sold==amount)
					{
						reject=(float) 0.5;
					}
					else
					{
						reject=amount-sold;
					}
					float rate = sold/reject;
					
					int tot= (int) (0.8*eff+0.2*rate);
					total.add(custWithSold.get(j).substring(0, custWithSold.get(j).indexOf(" "))+" "+tot);
						
					j++;
				}
				else
				{
					total.add(custWithAmount.get(i).substring(0, custWithAmount.get(i).indexOf(" "))+" "+"0");
				}
			}
			return total;
			
	}

}

// Rate sold/reject
// Eff  sold/amount